package com.wu.dms.dao.sqlProvide;

import com.wu.constant.Enum.UserStatusEnum;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.repository.query.Param;


/**
 * @author Cousin
 * @date 2025/1/7
 * @Description
 */
public class UserSqlProvider {
    public String queryUserCollectInPage(
            @Param("userId") Long userId,
            @Param("pageNum") Integer pageNum,
            @Param("pageSize") Integer pageSize,
            @Param("searchWord") String searchWord,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate) {
        int offset = (pageNum - 1) * pageSize;
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT resource_id as resourceId, ")
                .append("resource_type as resourceType, ")
                .append("resource_name as resourceName, ")
                .append("create_time as collectTime ")
                .append("FROM collect ")
                .append("WHERE user_id = #{userId} ");

        // 筛选搜索词
        if (StringUtils.isNotEmpty(searchWord)) {
            sql.append("AND resource_name LIKE CONCAT('%', #{searchWord}, '%') ");
        }

        // 筛选开始日期
        if (StringUtils.isNotEmpty(startDate)) {
            sql.append("AND create_time >= #{startDate} ");
        }

        // 筛选结束日期
        if (StringUtils.isNotEmpty(endDate)) {
            sql.append("AND create_time <= #{endDate} ");
        }

        // 添加排序，假设按收藏时间降序
        sql.append(" ORDER BY create_time DESC ");

        // 添加分页限制
        sql.append(" LIMIT ").append(offset).append(", #{pageSize}");

        return sql.toString();
    }
    public String countUserCollectInPage(
            @Param("userId") Long userId,
            @Param("searchWord") String searchWord,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT count(*) ")
                .append("FROM collect ")
                .append("WHERE user_id = #{userId} ");
        // 筛选搜索词
        if (StringUtils.isNotEmpty(searchWord)) {
            sql.append("AND resource_name LIKE CONCAT('%', #{searchWord}, '%') ");
        }

        // 筛选开始日期
        if (StringUtils.isNotEmpty(startDate)) {
            sql.append("AND create_time >= #{startDate} ");
        }

        // 筛选结束日期
        if (StringUtils.isNotEmpty(endDate)) {
            sql.append("AND create_time <= #{endDate} ");
        }


        return sql.toString();
    }
    public String queryUserInPage(Integer pageNum, Integer pageSize, String searchWord, String startDate, String endDate,Integer status) {
        int offset = (pageNum - 1) * pageSize;
        String sql = """
                SELECT
                    user.id,
                    user.name,
                    user.create_time,
                    latest_punishment.ban_time,
                    COALESCE(latest_punishment.status, 0) AS status
                FROM
                    user
                        LEFT JOIN
                    (
                        SELECT
                            user_id,
                            status,
                            ban_time
                        FROM
                            music.user_punishment_records
                        WHERE
                            (user_id, create_time) IN
                            (SELECT
                                 user_id,
                                 MAX(create_time)
                             FROM
                                 music.user_punishment_records where `delete` = 0
                             GROUP BY
                                 user_id)
                    ) AS latest_punishment
                    ON
                        user.id = latest_punishment.user_id
                WHERE user.name like '#{name}'\s
                """;
        if (StringUtils.isNotEmpty(startDate)){
            sql+= " AND user.create_time >= "+startDate;
        }
        if (StringUtils.isNotEmpty(endDate)){
            sql+= " AND user.create_time <= "+endDate;
        }
        sql += "ORDER BY user.create_time DESC\n" +
                "LIMIT #{offer}, #{size}";
                searchWord = "%" + searchWord +"%";
                sql = sql.replace("#{name}", searchWord);
                sql = sql.replace("#{offer}", String.valueOf(offset));
                sql = sql.replace("#{size}", String.valueOf(pageSize));
        return sql;
    }
    public String countUserInPage(String searchWord, String startDate, String endDate,String status) {
        Integer code = UserStatusEnum.getCodeByStatus(status);
        String sql = """
                SELECT
                    count(*)
                FROM
                    user
                WHERE user.name like '#{name}'\s
                """;
        if (StringUtils.isNotEmpty(startDate)){
            sql+= " AND user.create_time >= "+startDate;
        }
        if (StringUtils.isNotEmpty(endDate)){
            sql+= " AND user.create_time <= "+endDate;
        }
        sql = sql.replace("#{name}", searchWord);
        return sql;
    }

}
